/***********************************************************************************************************************************
 *    Name:	Configure_System.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary:	Set the properties for the system databases and memory.
**/

-- master
PRINT N'master';
ALTER DATABASE master SET RECOVERY SIMPLE;
USE master; CHECKPOINT;
DBCC SHRINKDATABASE ('master') WITH NO_INFOMSGS;
ALTER DATABASE master MODIFY FILE (NAME = N'master',  SIZE= 64MB, FILEGROWTH= 64MB, MAXSIZE= UNLIMITED);
ALTER DATABASE master MODIFY FILE (NAME = N'mastlog', SIZE=  8MB, FILEGROWTH=  8MB, MAXSIZE= UNLIMITED);
GO

-- model
PRINT N'model';
ALTER DATABASE model SET RECOVERY SIMPLE;
USE model; CHECKPOINT;
DBCC SHRINKDATABASE ('model') WITH NO_INFOMSGS;
ALTER DATABASE model MODIFY FILE (NAME = N'modeldev', SIZE= 64MB, FILEGROWTH= 64MB, MAXSIZE= UNLIMITED);
ALTER DATABASE model MODIFY FILE (NAME = N'modellog', SIZE=  8MB, FILEGROWTH=  8MB, MAXSIZE= UNLIMITED);
GO

-- msdb
PRINT N'msdb';
ALTER DATABASE msdb SET RECOVERY SIMPLE;
USE msdb; CHECKPOINT;
DBCC SHRINKDATABASE ('msdb') WITH NO_INFOMSGS;
ALTER DATABASE msdb MODIFY FILE (NAME = N'MSDBData', SIZE=128MB, FILEGROWTH= 64MB, MAXSIZE= UNLIMITED);
ALTER DATABASE msdb MODIFY FILE (NAME = N'MSDBLog',  SIZE=  8MB, FILEGROWTH=  8MB, MAXSIZE= UNLIMITED);
GO

-- Reporting Services
IF DB_ID('ReportServer') IS NOT Null BEGIN;
  PRINT N'ReportServer';
  ALTER DATABASE ReportServer SET RECOVERY SIMPLE;
  EXECUTE (N'USE [ReportServer]; CHECKPOINT;');
  DBCC SHRINKDATABASE('ReportServer') WITH NO_INFOMSGS;
  ALTER DATABASE ReportServer MODIFY FILE (NAME = N'ReportServer',     SIZE= 64MB, FILEGROWTH=64MB, MAXSIZE= UNLIMITED);
  ALTER DATABASE ReportServer MODIFY FILE (NAME = N'ReportServer_log', SIZE=  8MB, FILEGROWTH= 8MB, MAXSIZE= UNLIMITED);
END;
GO

-- Reporting Services
IF DB_ID('ReportServerTempDB') IS NOT Null BEGIN;
  PRINT N'ReportServerTempDB';
  ALTER DATABASE ReportServerTempDB SET RECOVERY SIMPLE;
  DBCC SHRINKDATABASE('ReportServerTempDB') WITH NO_INFOMSGS;
  EXECUTE (N'USE [ReportServerTempDB]; CHECKPOINT;');
  ALTER DATABASE ReportServerTempDB MODIFY FILE (NAME = N'ReportServerTempDB'    , SIZE = 64MB, FILEGROWTH=64MB, MAXSIZE= UNLIMITED);
  ALTER DATABASE ReportServerTempDB MODIFY FILE (NAME = N'ReportServerTempDB_log', SIZE =  8MB, FILEGROWTH= 8MB, MAXSIZE= UNLIMITED);
END;
GO

-- TempDB default single data file
PRINT N'TempDB';
USE master;
ALTER DATABASE TempDB MODIFY FILE (NAME= TempLog, SIZE= 1GB, FILEGROWTH= 1GB, MAXSIZE= UNLIMITED, NEWNAME= TempDB_Log);
ALTER DATABASE TempDB MODIFY FILE (NAME= TempDev, SIZE= 1GB, FILEGROWTH= 1GB, MAXSIZE= UNLIMITED, NEWNAME= TempDB0);
GO


/***********************************************************************************************************************************
 * Configure TempDB

DECLARE
  @cores			TINYINT,
  @n				TINYINT,
  @tsql_template	NVARCHAR(MAX),
  @tsql_command		NVARCHAR(MAX);
SELECT @cores= 4, @n= 1, @tsql_template= N'ALTER DATABASE TempDB ADD    FILE (NAME= TempDB?, SIZE= 1GB, FILEGROWTH= 1GB, MAXSIZE= UNLIMITED, FILENAME= ''' + master.DBAdmin.GetInstancePath('DefaultData') + N'TempDB?.ndf'');';

SET @tsql_command= N'ALTER DATABASE TempDB MODIFY FILE (NAME= TempDB_Log, FILENAME= ''' + DBAdmin.GetInstancePath('DefaultLog') + N'TempDB.ldf''); ALTER DATABASE TempDB MODIFY FILE (NAME= TempDB0, FILENAME= ''' + DBAdmin.GetInstancePath('DefaultData') + N'TempDB0.mdf'')';
PRINT @tsql_command;

WHILE @n < @cores BEGIN;
  SET @tsql_command= REPLACE(@tsql_template, N'?', CAST(@n AS NVARCHAR(3)));
  PRINT @tsql_command;
  SET @n= @n + 1;
END;

EXECUTE sp_configure 'max degree of parallelism', @cores;
EXECUTE sp_configure 'max server memory (MB)',  8192;
RECONFIGURE;
**/
GO